package com.trip.wechat.platform.dao.impl;

import com.trip.wechat.platform.dao.UserDao;
import com.trip.wechat.platform.domain.UserPO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Optional;

/**
 * 功能说明: 微信用户DAO<br>
 * 系统说明: <br>
 * 模块说明: <br>
 * 功能描述: <br>
 */
@Repository
@Transactional
public class UserDaoImpl implements UserDao {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Override
    @Transactional
    public void merge(UserPO userPO) {
        if (userPO.getSubscribe() == null || userPO.getSubscribe() == 0) {
            jdbcTemplate.update(UN_SUBSCRIBE, new Object[] {userPO.getOpenid(), userPO.getOpenid(), userPO.getPublicKey()});
        } else {
            jdbcTemplate.update(U_MERGE, new Object[] {
                    userPO.getOpenid(),
                    userPO.getUnionid(), userPO.getNickname(), userPO.getSex(),
                    userPO.getCity(), userPO.getCountry(), userPO.getProvince(),
                    userPO.getHeadImgUrl(), userPO.getSubscribe(), userPO.getLanguage(),
                    userPO.getGroupid(), userPO.getRemark(), userPO.getSubscribeTime(),
                    userPO.getSubscribeScene(), userPO.getQrScene(), userPO.getQrSceneStr(),
                    userPO.getOpenid(), userPO.getPublicKey(),
                    userPO.getUnionid(), userPO.getNickname(), userPO.getSex(),
                    userPO.getCity(), userPO.getCountry(), userPO.getProvince(),
                    userPO.getHeadImgUrl(), userPO.getSubscribe(), userPO.getLanguage(),
                    userPO.getGroupid(), userPO.getRemark(), userPO.getSubscribeTime(),
                    userPO.getSubscribeScene(), userPO.getQrScene(), userPO.getQrSceneStr()
            });
        }
    }

    @Override
    public List<UserPO> selectByPublicKey(String publicKey) {
        return jdbcTemplate.query(Q_BY_PUBLICKEY, new Object[] {publicKey}, (rs, i) -> {
            UserPO po = new UserPO();
            po.setOpenid(rs.getString("openid"));
            po.setPublicKey(rs.getString("publickey"));
            po.setUnionid(rs.getString("unionid"));
            po.setNickname(rs.getString("nickname"));
            po.setCity(rs.getString("city"));
            po.setCountry(rs.getString("country"));
            po.setProvince(rs.getString("province"));
            po.setHeadImgUrl(rs.getString("headimgurl"));
            po.setSubscribe(rs.getInt("subscribe"));
            po.setLanguage(rs.getString("language"));
            po.setGroupid(rs.getInt("groupid"));
            po.setRemark(rs.getString("remark"));
            po.setSubscribeTime(rs.getString("subscribe_time"));
            po.setSubscribeScene(rs.getString("subscribe_scene"));
            po.setQrScene(rs.getString("qr_scene"));
            po.setQrSceneStr(rs.getString("qr_scene_str"));
            return po;
        });
    }

    @Override
    @Cacheable(value = "wx_user_info_cache", key = "#publicKey + '.' + #openid")
    public Optional<UserPO> selectUserInfoByOpenid(String publicKey, String openid) {
        return jdbcTemplate.query(Q_BY_OPENID,
            ps -> {
                ps.setString(1, publicKey);
                ps.setString(2, openid);
            },
            (rs, i) -> {
                UserPO po = new UserPO();
                po.setOpenid(rs.getString("openid"));
                po.setPublicKey(rs.getString("publickey"));
                po.setUnionid(rs.getString("unionid"));
                po.setNickname(rs.getString("nickname"));
                po.setSex(rs.getInt("sex"));
                po.setCity(rs.getString("city"));
                po.setCountry(rs.getString("country"));
                po.setProvince(rs.getString("province"));
                po.setHeadImgUrl(rs.getString("headimgurl"));
                po.setSubscribe(rs.getInt("subscribe"));
                po.setLanguage(rs.getString("language"));
                po.setGroupid(rs.getInt("groupid"));
                po.setRemark(rs.getString("remark"));
                po.setSubscribeTime(rs.getString("subscribe_time"));
                po.setSubscribeScene(rs.getString("subscribe_scene"));
                po.setQrScene(rs.getString("qr_scene"));
                po.setQrSceneStr(rs.getString("qr_scene_str"));
                return po;
            }).stream().findFirst();
    }

    final static String UN_SUBSCRIBE = "merge into wechat_users p1 " +
            "using (select ? as openid from dual) p2 on (p1.openid=p2.openid) " +
            "when matched then " +
            "  update set p1.subscribe = 0 " +
            "when not matched then " +
            "  insert(openid, publickey, subscribe, created) values(?, ?, 0, sysdate)";
    final static String U_MERGE = "merge into wechat_users p1 " +
            "using (select ? as openid from dual) p2 on (p1.openid=p2.openid) " +
            "when matched then " +
            "  update set p1.unionid=?, p1.nickname=?, p1.sex=?, p1.city=?, p1.country=?, p1.province=?, " +
            "  p1.headimgurl=?, p1.subscribe=?, p1.language=?, p1.groupid=?, p1.remark=?,  " +
            "  p1.subscribe_time=?, p1.subscribe_scene=?, p1.qr_scene=?, p1.qr_scene_str=?, p1.synced = sysdate " +
            "when not matched then " +
            "  insert(openid, publickey, unionid, nickname, sex, city, country, province, " +
            "  headimgurl, subscribe, language, groupid, remark, " +
            "  subscribe_time, subscribe_scene, qr_scene, qr_scene_str, synced, created) " +
            "  values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, sysdate, sysdate)";

    final static String Q_BY_PUBLICKEY = "select openid,publickey, unionid, nickname, sex, city, country, province, " +
            "headimgurl, subscribe, language, groupid, remark, subscribe_time, subscribe_scene, qr_scene, qr_scene_str " +
            "from wechat_users where publickey=?";

    final static String Q_BY_OPENID = "select openid,publickey, unionid, nickname, sex, city, country, province, " +
            "headimgurl, subscribe, language, groupid, remark, subscribe_time, subscribe_scene, qr_scene, qr_scene_str " +
            "from wechat_users where publickey=? and openid=?";
}
